经典Oracle的sql语句百例训练汇总.docx |
您所在的位置:网站首页 › SQL基础语句汇总 where › 经典Oracle的sql语句百例训练汇总.docx |
经典Oracle的sql语句百例训练汇总.docx 《经典Oracle的sql语句百例训练汇总.docx》由会员分享,可在线阅读,更多相关《经典Oracle的sql语句百例训练汇总.docx(33页珍藏版)》请在冰豆网上搜索。 经典Oracle的sql语句百例训练汇总 Oracle系列《一》: 简单SQL与单行函数 使用s cott/tiger用户下的emp表和dept表完成下列练习,表的结构说明如下 emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号) dept部门表(deptno部门编号/dname部门名称/loc地点) 工资=薪金+佣金 登录Oracle数据库 1、sqlplusscott/tiger 2、sqlplus/nolog SQL>connscott/tiger 若是使用SYS的账号进行登录的话,则使用以下语句 SQL>conn/assysdba
【1】EMP表内容查询 SQL>SELECT*FROMemp; 出错,原因是没有找到该表,因为该表时SCOTT用户的表,所以查询时应该加上scott.emp就可以了 【2】显示当前用户 SQL>showuser 【3】查看当前用户的所有表 SQL>SELECT*FROMtab; 【4】若想重复执行上一条SQL语句,则在sqlplus命令行下输入"/"即可 【5】查询一张表的结构,例如dept表 SQL>descdept 【6】在雇员表中查询雇员的编号、姓名、工作 SQL>SELECTempno,ename,jobFROMemp; 【7】可以为列名取别名,在Linux下Oracle如果英文别名不加上双引号则会变成大写 SQL>SELECTempno编号,ename姓名,job工作FROMemp; 【8】查询所有的工作 SQL>SELECTDISTINCTjobFROMemp; 工作可能会重复,加上DISTINCT关键字 【9】若要求按照以下的格式进行结果输出,如NO: 7469,Name: SMITH,Job: CLERK SQL>SELECT'NO: '||empno||',Name: '||ename||',Job: '||jobFROMemp; 【10】要求列出每个雇员的姓名及年薪 SQL>SELECTename,sal*12incomeFROMemp; 这里年薪最好用别名进行标识,可以一眼就能明白 【11】查看每月可以得到奖金的雇员信息 SQL>SELECT*FROMempWHEREcommisNOTNULL; 【12】要求基本工资大于1500,同时可以领取奖金的雇员信息 SQL>SELECT*FROMempWHEREsal>1500ANDcommisNOTNULL; 如果是或的是关系,则使用OR 【13】查询基本工资不大于1500,同时不可以领取奖金的雇员信息 SQL>SELECT*FROMempWHERENOT(sal>1500ANDcommisNOTNULL); 【14】查询在1981年雇佣的全部雇员信息,BETWEEN..AND包含等于的情况 SQL>SELECT*FROMemp WHEREhiredateBETWEEN'01-JAN-81'AND'31-DEC-81'; 【15】Oracle对大小敏感,所以查询时名字要区分大小写 【16】要求查询出雇员编号不是7369、7499的雇员信息 SQL>SELECT*FROMemp WHEREempnoNOTIN(7369,7499); 【17】SQL中LIKE语句要注意通配符%和_ SQL>SELECT*FROMemp WHEREhiredateLIKE'%81%'; 【18】查看雇员编号不是7369的雇员信息,使用或! = SQL>SELECT*FROMemp WHEREempno7369; 【19】要求对雇员的工资由低到高进行排序,升序为默认(ASC),降序(DESC) SQL>SELECT*FROMemp GROUPBYsal; 【20】查看出部门号为10的雇员信息,查询的信息按照工资从高到低,若工资相等则按雇用日期从早到晚排列 SQL>SELECT*FROMemp WHEREdeptno=10 GROUPBYsalDESC,hiredateASC; 数据库系统中,每个数据库之间区别最大的就是在函数的支持上,单行函数是最简单的函数,单行函数分为 1、字符函数: 接受字符输入并且返回字符或数值 2、数值函数: 接受数值输入并返回数值 3、日期函数: 对日期型数据进行操作 4、转换函数: 将一种数据类型转换为另一种数据类型 5、通用函数: NVL、DECODE函数 字符函数: 【1】大小写转换UPPER和LOWER SQL>SELECTUPPER('smith')FROMdual; 【2】将雇员姓名变为开头字母大写,INITCAP SQL>SELECTINITCAP(ename)FROMemp; 字符函数中有连接函数CONCAT,但不如||好用,还有字符串处理的一些函数 字符串截取: substr() 字符串长度: length() 内容替换: replace() SQL>SELECTsubstr('hello',1,3),length('hello'),replace('hello','l','x')FROMdual; 这里注意的是Oracle中字符串截取从0和从1开始都是一样的,谨防面试提问 【3】要求显示所有雇员的姓名及姓名的后3个字符 SQL>SELECTename,SUBSTR(ename,LENGTH(ename)-2)FROMemp; 以上操作显得较为麻烦,substr()函数是可以倒着截取 SQL>SELECTename,SUBSTR(ename,-3,3)FROMemp; 数值函数: 1、四舍五入: ROUND() 2、截断小数位: TRUNC() 3、取余(取模): MOD SQL>SELECTROUND(789.536)FROMdual; 【1】保留2位小数,(如果是-2则对整数进行四舍五入,变为800了) SQL>SELECTROUND(783.56,2)FROMdual; 【2】使用MOD()函数进行取余操作 SQL>SELECTMOD(10,3)FROMdual; 日期函数: 1、日期-数字=日期 2、日期+数字=日期 3、日期-日期=数字(天数) 【1】求出当前日期 SQL>SELECTSYSDATEFROMdual; Oracle提供了以下的日期函数支持: MONTHS_BETWEEN(): 求出给定日期范围的月数 ADD_MONTHS(): 在指定日期上加上指定的月数,求出之后的日期 NEXT_DAY(): 下一个的今天的日期 LAST_DAY(): 求出给定日期的最后一天日期 【2】求出从雇用日期到今天所有雇员的雇员编号、姓名和月数 SQL>SELECTempno,ename,ROUND(MONTHS_BETWEEN(SYSDATE,hiredate))FROMemp; 【3】验证ADD_MONTHS()、NEXT_DAY()、LAST_DAY() SQL>SELECTADD_MONTHS(SYSDATE,4)FROMDUAL; SQL>SELECTNEXT_DAY(SYSDATE,'MON')FROMDUAL; SQL>SELECTLAST_DAY(SYSDATE)FROMDUAL; 转换函数: 1、TO_CHAR(): 将日期或数值转换成字符串 2、TO_NUMBER(): 将字符串转换成数字 3、TO_DATE(): 将字符串转换成日期 【1】将年月日进行分开,要指定拆分的通配符,yyyy-mm-dd SQL>SELECTempno,ename,TO_CHAR(hiredate,'yyyy')datetimeFROMemp; 【2】将薪水的数字进行格式化,'$99,999'表示美元,'L99,999'表示当地货币 SQL>SELECTempno,ename,TO_CHAR(sal,'99,999)salaryFROMemp; 【3】TO_NUMBER()验证 SQL>SELECTTO_NUMBER('123')+TO_NUMBER('123')FROMDUAL; 【4】TO_DATE()验证,如下例子执行后显示为11-JUL-11 SQL>SELECTTO_DATE('2011-7-11','yyyy-mm-dd')FROMDUAL; 通用函数: 【1】求出每个雇员的年薪(应算上奖金) SQL>SELECTempno,ename,(sal+comm)*12FROMemp; 由于comm中有NULL,NULL值计算后还是NULL,正确如下: SQL>SELECTempno,ename,NVL(comm,0),(sal+NVL(comm,0))*12incomeFROMemp; NVL可以理解为将NULL值转换为具体的内容,这里是0 【2】DECODE()函数,该函数类似于IF...ELSEIF...ELSE 语法如下: DECODE(col/expression,选择1,结果1[,选择2,结果2,...,默认]) 验证DECODE()函数 SQL>SELECTempno,ename,hiredate, DECODE(job,'CLERK','业务员','SALESMAN','销售人员',’MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁')职位 FROMemp; SQL简单语句练习: 【1】找出佣金高于薪金的60%的员工 SQL>SELECT*FROMempWHEREcomm>sal*0.6 【2】找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料 SQL>SELECT*FROMemp WHERE(deptno=20ANDjob='MANAGER') OR(deptno=10ANDjob='CLERK'); 【3】找出既不是经理又不是办事员但其薪金大于或等于2000的所有员工的资料 SQL>SELECT*FROMemp WHEREjobNOTIN('MANAGER','CLERK')ANDsal>=2000; 【4】找出有奖金的员工的不同国祚 SQL>SELECTDISTINCTjobFROMemp WHEREcommISNOTNULL; 【5】找出各月倒数第3天受雇的所有员工 SQL>SELECT*FROMemp WHERELAST_DAY(hiredate)-2=hiredate; 【6】找出早于12年前受雇的员工 SQL>SELECT*FROMemp WHEREMONTHS_BETWEEN(sysdate,hiredate)/12>12; 【7】显示刚好为5个字符的员工的姓名 SQL>SELECTenameFROMemp WHERElength(ename)=5; 【8】显示不带有"R"的员工的姓名 SQL>SELECTenameFROMemp WHEREenameNOTLIKE'%R%'; 【9】显示员工的姓名和受雇日期,将最老的员工排在最前 SQL>SELECT*FROMemp GROUPBYhiredate; 【10】显示所有员工的姓名,加入公司的年份和月份,按受雇日期所在月排序,若月份相同则按年份排序 SQL>SELECTename,TO_CHAR(hiredate,'yyyy')year,TO_CHAR(hiredate,'mm')monthFROMemp ORDERBYmonth,year; 【11】找出在2月受聘的员工 SQL>SELECT*FROMemp WHERETO_CHAR(hiredate,'mm')=2; 【12】以年月日方式显示所有员工服务年限 SQL>SELECTename,TRUNC(MONTHS_BETWEEN(sysdate,hiredate)/12)year, TRUNC(MOD(MONTHS_BETWEEN(sysdate,hiredate),12))month, TRUNC(MOD(sysdate-hiredate,30))day FROMemp; Oracle系列《二》: 多表复杂查询和事务处理 多表查询应该注意去除笛卡尔积,一般多个表时会为表起个别名 【1】要求查询雇员的编号、姓名、部门编号、部门名称及部门位置 SQL>SELECTe.empno,e.ename,d.deptno,d.dname,d.loc FROMempe,deptd WHEREe.deptno=d.deptno; 【2】要求查询每个雇员的姓名、工作、雇员的直接上级领导的姓名(表自关联) SQL>SELECTe.ename,e.job,m.ename FROMempe,empm WHEREe.mgr=m.empno; 【3】对【2】进行扩充,将雇员所在部门名称同时列出 SQL>SELECTe.ename,e.job,m.ename,d.dname FROMempe,empm,deptd WHEREe.mgr=m.empnoANDe.deptno=d.deptno; 【4】查询每个雇员的姓名、工资、部门名称,工资在公司的等级(salgrade),及其领导的姓名所在公司的等级 先确定工资等级表的内容 SQL>SELECT*FROMsalgrade; 查询每个雇员的姓名、工资、部门名称和工资在公司的等级 SQL>SELECTe.ename,e.sal,d.dname,s.grade FROMempe,deptd,salgrades WHEREe.deptno=d.deptnoANDe.salBETWEENs.losalANDs.hisal; 查询其领导姓名及工资所在公司的等级 SQL>SELECTe.ename,e.sal,d.dname,s.grade,m.ename,m.sal,ms.grade FROMempe,deptd,salgrades,empm,salgradems WHEREe.deptno=d.deptnoANDe.salBETWEENs.losalANDs.hisal ANDe.mgr=m.empnoANDm.salBETWEENms.losalANDms.hisal; 【5】左连接与右连接的概念,"+"在等号左边表示右连接,反之,左连接 查询雇员的编号、姓名及其领导的编号、姓名 SQL>SELECTe.empno,e.ename,m.empno,m.ename FROMempe,empm WHEREe.mgr=m.empno(+); 就发现将KING的那条记录也连过来了 SQL1999语法中有如下几种连接(了解) 1、交叉连接CROSSJOIN,产生笛卡尔积 SQL>SELECT*FROMempCROSSJOINdept; 2、自然连接NATURALJOIN,自动进行关联字段的匹配 SQL>SELECT*FROMempNATURALJOINdept; 3、使用USING子句,直接关联操作列 SQL>SELECT*FROMempJOINdeptUSING(deptno) WHEREdeptno=30; 4、使用ON子句,用户自己编写的条件 SQL>SELECT*FORMempJOINdeptON(emp.deptno=dept.deptno) WHEREdeptno=30; 5、左连接(左外连接、LEFT(OUTER)JOIN)、右连接(右外连接、RIGHT(OUTER)JOIN) 组函数及分组统计 1、COUNT(): 求出全部记录数 2、MAX(): 求出一组中最大值 3、MIN(): 求出最小值 4、AVG(): 求出平均值 5、SUM(): 求和 【1】求出每个部门的雇员数量 SQL>SELECTdeptno,count(empno) FROMemp GROUPBYdeptno; 【2】按部门分组,并显示部门的名称,及每个部门的员工数 SQL>SELECTd.dname,COUNT(e.empno) FROMempe,deptd WHEREe.deptno=d.deptno GROUPBYd.dname; 【3】要求显示平均工资大于2000的部门编号和平均工资 SQL>SELECTdeptno,AVG(sal) FROMemp WHEREAVG(sal)>2000 GROUPBYdeptno; 出错,WHERE子句中不能出现分组函数的条件,要使用HAVING子句 上述语句应该改为如下 SQL>SELECTdeptno,AVG(sal) FROMemp GROUPBYdeptno HAVINGAVG(sal)>2000 【4】显示非销售人员工作名称以及从事同一工作雇员的月工资总和,并且要求从事同一工作的雇员月工资合计大于$5000, 输出结果按月工资的合计升序排序 按工作分组,求出非销售人员的月工资总和 SQL>SELECTjob,SUM(sal) FROMemp WHEREjob'SALESMAN' GROUPBYjob; 对分组条件进行限制,然后进行排序,HAVING子句不能使用别名 SQL>SELECTjob,SUM(sal)totalSal FROMemp WHEREjob'SALESMAN' GROUPBYjob HAVINGSUM(sal)>5000 ORDERBYtotalSal; 【3】分组函数可以嵌套使用,但是在SELECT列中就不能再出现该分组条件的列名了 SQL>SELECTdeptno,MAX(AVG(sal)) FROMemp GROUPBYdeptno; 出错! 修改如下 SQL>SELECTMAX(AVG(sal)) FROMemp GROUPBYdeptno; 【4】查询出比7654工资要高的全部雇员的信息 首先要查询雇员编号7654的工资 SQL>SELECTsalFROMempWHEREempno=7654; 以上述条件的结果最后后续查询的依据 SQL>SELECT*FROMemp WHEREsal>(SELECTsalFROMempWHEREempno=7654); 子查询在操作中分为以下三类: 1、单列子查询: 返回的结果是一列的内容 2、单行子查询: 返回多个列,也可能是一条记录 3、多行子查询: 返回多个记录 【1】要求查询工资比7654高,同时与7788从事相同工作的全部雇员 SQL>SELECT*FROMemp WHEREsal>(SELECTsalFROMempWHEREempno=7654) ANDjob=(SELECTjobFROMempWHEREempno=7788); 【2】要求查询部门名称、部门员工数、部门平均工资,部门的最低收入雇员的姓名 查询部门员工数、部门平均工资 SQL>SELECTdeptno,COUNT(empno),AVG(sal) FROMemp GROUPBYdeptno; 查询部门的名称,及最低收入雇员姓名,要进行表关联(子查询) SQL>SELECTd.dname,ed.c,ed.a,e.ename FROMdeptd,( SELECTdeptno,COUNT(empno)c,AVG(sal)a,MIN(sal)min FROMemp GROUPBYdeptno)ed,empe WHEREd.deptno=ed.deptnoANDe.sal=ed.min; 若上述存在两个最低工资的情况,则会出错,在子查询中存在以下3种查询的操作符号 IN: 指定一个查询范围,例如查询每个部门的最低工资(返回值有多个) SQL>SELECT*FROMemp WHEREsalIN(SELECTMIN(sal)FROMempGROUPBYdeptno); ANY: =ANY(与IN操作一样)、>ANY(比最小大)、 SQL>SELECT*FROMemp WHEREsal ALL: >ALL(比最大要大)、 SQL多列子查询示例如下 SQL>SELECT*FROMemp WHERE(sal,NVL(comm,-1))IN (SELECTsal,NVL(comm,-1)FROMempWHEREdeptno=20);
数据库更新操作INSERT、UPDATE、DELETE 【1】复制一张表,例如复制EMP表为MYEMP SQL>CREATETABLEMYTEMPASSELECT*FROMemp; 【2】将编号为7899的雇员的领导取消 SQL>UPDATEmyempSETmgr=nullWHEREempno=7899; 【3】更新时,一定要注意不能批量更新(加上WHERE子句),多列更新例子如下 SQL>UPDATEmyempSETmgr=null,comm=nullWHEREempnoIN(7369,8899); 【4】删除掉全部领取奖金的雇员 SQL>DELECTFROMempWHEREcommisNOTNULL; 事务处理ACID A: Atomicity 原子性: 事务中的操作或者都完成,或者都取消 C: Consistency一致性: 事务中的操作保证数据库中的数据不会出现逻辑上不一致的情况 I: Isolation 隔离性: 当前的事务与其他未完成的事务是隔离的 D: Durability 持久性: 在COMMIT之后,数据永久保存在数据库中,在此之前,事务的操作都可以回滚 验证事务过程: 创建一张临时表,只包含部门10 SQL>CREATETABLEemp10ASSELECT*FROMempWHEREempno=10; 删除emp10中的7782雇员 SQL>DELETEFROMemp10WHEREempno=7782; 再打开另 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |